package com.sportswork.sportswork.controller.admin;

/**
 * @author dengwei
 * @date 2020/2/3 13:53
 * @description
 */

import com.alibaba.druid.support.json.JSONParser;
import com.alibaba.druid.support.json.JSONWriter;
import com.sportswork.sportswork.config.SecurityUserInfo;
import com.sportswork.sportswork.core.entity.*;
import com.sportswork.sportswork.core.service.dto.UserDTO;
import com.sportswork.sportswork.core.service.impl.*;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Slf4j
@Controller("exportController")
public class ExportController {
    @Resource
    private UserServiceImp userServiceImp;
    @Resource
    private MenuServiceImp menuServiceImp;
    @Resource
    private StudentServiceImp studentServiceImp;
    @Resource
    private TeacherServiceImp teacherServiceImp;
    @Resource
    private KtinfoServiceImp ktinfoServiceImp;
    @Resource
    private DkcjServiceImp dkcjServiceImp;
    @Resource
    private CreditServiceImp creditServiceImp;

    /*下载学生模板*/
    @SuppressWarnings({ "rawtypes", "unchecked" })
    @RequestMapping(value = "/admin/student/export")
    public void export(HttpServletRequest request, HttpServletResponse response)
            throws IOException {
        Map<String, Object> o = ( Map<String, Object>)doExcelByMb();
        try {
            String fileName = (String) o.get("fileName");
            List<Map<String, Object>> list = (List<Map<String, Object>>) o.get("list");
            String cols[] = (String[]) o.get("cols");// 列名
            String keys[] = (String[]) o.get("keys");// map中的key
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            try {
                createWorkBook(list, keys, cols).write(os);
            } catch (IOException e) {
                e.printStackTrace();
            }
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            // 设置response参数，可以打开下载页面
            response.reset();
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + new String(fileName.getBytes(), "iso-8859-1"));
            ServletOutputStream out = response.getOutputStream();
            BufferedInputStream bis = null;
            BufferedOutputStream bos = null;
            try {
                bis = new BufferedInputStream(is);
                bos = new BufferedOutputStream(out);
                byte[] buff = new byte[2048];
                int bytesRead;
                // Simple read/write loop.
                while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                    bos.write(buff, 0, bytesRead);
                }
            } catch (final IOException e) {
                throw e;
            } finally {
                if (bis != null)
                    bis.close();
                if (bos != null)
                    bos.close();
            }
        }  catch (SecurityException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }  catch (IllegalArgumentException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }


    private String message = "";
    /**
     * 封装读取excel文件  存储数据库
     * @param excelFile  @lys
     * @return
     * @throws IOException
     */
    @SuppressWarnings({ "rawtypes", "unchecked" })
    @ResponseBody
    @RequestMapping(value = "/admin/student/exports",produces="application/json; charset=utf-8")
    public String readXls( MultipartFile[] excelFile,HttpServletRequest request, HttpServletResponse response) {
        Map<String, Object> o = new HashMap<String, Object>();
        long startTime=System.currentTimeMillis();
        String originalFilename;

        try {
            if (excelFile == null || excelFile.length == 0) {
                return this.toJson("002", "文件上传失败，请重试！");
            }

            for (MultipartFile myfile : excelFile) {
                if (myfile.isEmpty()) {
                    return this.toJson("002", "文件上传失败，请重试！");
                } else {
                    originalFilename = myfile.getOriginalFilename();
                    if (!(originalFilename.endsWith(".xls") || originalFilename.endsWith(".xlsx"))) {
                        return toJson("002", "导入失败");
                    }

                    List<Map<String, Object>> list = null;
                    /*根据文件格式解析文件*/
                    if (originalFilename.endsWith(".xls")) {
                        list = readXLS(myfile);
                    } else if (originalFilename.endsWith(".xlsx")) {
                        list = readXLSX(myfile);
                    }
                    /* 获取数据 批量插入数据*/
                    if(list.size()>0){
                        return  insertXS(list);
                    }

                }
            }

            long hs=System.currentTimeMillis()-startTime;
            System.out.println("导入数据总耗时："+hs+"毫秒");

        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return "009";
        }
        return "000";

    }

    public String insertXS(List<Map<String, Object>> list){

        for (int i = 0; i < list.size() ; i++) {
            String xsxh = getValue(list.get(i).get("学生学号"));
            String xsxm = getValue(list.get(i).get("学生姓名"));
            String psw = getValue(list.get(i).get("密码"));
            String sex = getValue(list.get(i).get("性别"));
            String rxnj = getValue(list.get(i).get("入学年级"));
            String zy = getValue(list.get(i).get("专业"));
            String phone = getValue(list.get(i).get("联系电话"));
            String email = getValue(list.get(i).get("邮箱"));
            String sfz = getValue(list.get(i).get("身份证"));
            String address = getValue(list.get(i).get("住址"));
            if(xsxh.equals("")){
                return toJson("002", "第"+(i+1)+"行学生学号不可为空，请检查!");
            }
            if(xsxm.equals("")){
                return toJson("002", "第"+(i+1)+"行学生姓名不可为空，请检查!");
            }
            if(psw.equals("")){
                return toJson("002", "第"+(i+1)+"行学生密码不可为空，请检查!");
            }
            if(zy.equals("")){
                return toJson("002", "第"+(i+1)+"行学生专业不可为空，请检查!");
            }

            //因为没有用户名，名字可以重复，所以只判断手机号跟用户名存在的客户说明存在
            int result =  userServiceImp.pdUser(xsxh);
            System.out.println(result);
            if(result>0){
                //说明已存在、
                return toJson("002", "第"+(i+1)+"行学生学号已存在，请检查!");
            }
            //插入数据库
            Student student = new Student();
            student.setName(xsxm);
            student.setPhone(phone);
            student.setPassword(psw);
            student.setRxnj(rxnj);
            student.setSex(sex);
            student.setEmail(email);
            student.setSfz(sfz);
            student.setAddress(address);
            student.setNumber(xsxh);
            student.setProfession(zy);
            studentServiceImp.addStudent(student);
        }

        return toJson("000", "导入成功");
    }






    /*下载老师模板*/
    @SuppressWarnings({ "rawtypes", "unchecked" })
    @RequestMapping(value = "/admin/teacher/export2")
    public void export2(HttpServletRequest request, HttpServletResponse response) throws IOException {
        Map<String, Object> o = ( Map<String, Object>)doExcelByMb2();
        try {
            String fileName = (String) o.get("fileName");
            List<Map<String, Object>> list = (List<Map<String, Object>>) o.get("list");
            String cols[] = (String[]) o.get("cols");// 列名
            String keys[] = (String[]) o.get("keys");// map中的key
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            try {
                createWorkBook(list, keys, cols).write(os);
            } catch (IOException e) {
                e.printStackTrace();
            }
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            // 设置response参数，可以打开下载页面
            response.reset();
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + new String(fileName.getBytes(), "iso-8859-1"));
            ServletOutputStream out = response.getOutputStream();
            BufferedInputStream bis = null;
            BufferedOutputStream bos = null;
            try {
                bis = new BufferedInputStream(is);
                bos = new BufferedOutputStream(out);
                byte[] buff = new byte[2048];
                int bytesRead;
                // Simple read/write loop.
                while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                    bos.write(buff, 0, bytesRead);
                }
            } catch (final IOException e) {
                throw e;
            } finally {
                if (bis != null)
                    bis.close();
                if (bos != null)
                    bos.close();
            }
        }  catch (SecurityException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }  catch (IllegalArgumentException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    /*下载单科成绩导入模板*/
    @SuppressWarnings({ "rawtypes", "unchecked" })
    @RequestMapping(value = "/admin/dkcj/exportDkcj")
    public void exportDkcj(HttpServletRequest request, HttpServletResponse response) throws IOException {
        Map<String, Object> o = ( Map<String, Object>)doExcelByMbDkcj();
        try {
            String fileName = (String) o.get("fileName");
            List<Map<String, Object>> list = (List<Map<String, Object>>) o.get("list");
            String cols[] = (String[]) o.get("cols");// 列名
            String keys[] = (String[]) o.get("keys");// map中的key
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            try {
                createWorkBook(list, keys, cols).write(os);
            } catch (IOException e) {
                e.printStackTrace();
            }
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            // 设置response参数，可以打开下载页面
            response.reset();
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + new String(fileName.getBytes(), "iso-8859-1"));
            ServletOutputStream out = response.getOutputStream();
            BufferedInputStream bis = null;
            BufferedOutputStream bos = null;
            try {
                bis = new BufferedInputStream(is);
                bos = new BufferedOutputStream(out);
                byte[] buff = new byte[2048];
                int bytesRead;
                // Simple read/write loop.
                while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                    bos.write(buff, 0, bytesRead);
                }
            } catch (final IOException e) {
                throw e;
            } finally {
                if (bis != null)
                    bis.close();
                if (bos != null)
                    bos.close();
            }
        }  catch (SecurityException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }  catch (IllegalArgumentException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }


    /**
     * 封装读取excel文件  存储数据库   读取教师导入模板
     * @param excelFile  @lys
     * @return
     * @throws IOException
     */
    @SuppressWarnings({ "rawtypes", "unchecked" })
    @ResponseBody
    @RequestMapping(value = "/admin/teacher/exportjs",produces="application/json; charset=utf-8")
    public String exportjs( MultipartFile[] excelFile,HttpServletRequest request, HttpServletResponse response) {
        Map<String, Object> o = new HashMap<String, Object>();
        long startTime=System.currentTimeMillis();
        String originalFilename;

        try {
            if (excelFile == null || excelFile.length == 0) {
                return this.toJson("002", "文件上传失败，请重试！");
            }

            for (MultipartFile myfile : excelFile) {
                if (myfile.isEmpty()) {
                    return this.toJson("002", "文件上传失败，请重试！");
                } else {
                    originalFilename = myfile.getOriginalFilename();
                    if (!(originalFilename.endsWith(".xls") || originalFilename.endsWith(".xlsx"))) {
                        return toJson("002", "导入失败");
                    }

                    List<Map<String, Object>> list = null;
                    /*根据文件格式解析文件*/
                    if (originalFilename.endsWith(".xls")) {
                        list = readXLS(myfile);
                    } else if (originalFilename.endsWith(".xlsx")) {
                        list = readXLSX(myfile);
                    }
                    /* 获取数据 批量插入数据*/
                    if(list.size()>0){
                        return  insertXS2(list);
                    }

                }
            }

            long hs=System.currentTimeMillis()-startTime;
            System.out.println("导入数据总耗时："+hs+"毫秒");

        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return "009";
        }
        return "000";

    }

    public String insertXS2(List<Map<String, Object>> list){

        for (int i = 0; i < list.size() ; i++) {
            String jsbh = getValue(list.get(i).get("教师编号"));
            String jsxm = getValue(list.get(i).get("教师姓名"));
            String sex = getValue(list.get(i).get("性别"));
            String psw = getValue(list.get(i).get("密码"));

            String kc = getValue(list.get(i).get("课程"));
            String zw = getValue(list.get(i).get("职称"));
            String email = getValue(list.get(i).get("邮箱"));
            String phone = getValue(list.get(i).get("联系电话"));
            String sfz = getValue(list.get(i).get("身份证"));
            String address = getValue(list.get(i).get("住址"));

            if(jsbh.equals("")){
                return toJson("002", "第"+(i+1)+"行教师编号不可为空，请检查!");
            }
            if(jsxm.equals("")){
                return toJson("002", "第"+(i+1)+"行教师姓名不可为空，请检查!");
            }
            if(psw.equals("")){
                return toJson("002", "第"+(i+1)+"行教师密码不可为空，请检查!");
            }
            //判断教师的编号是否已经存在
            int result =  userServiceImp.pdUser(jsbh);
            if(result>0){
                //说明已存在、
                return toJson("002", "第"+(i+1)+"行教师编号已存在，请检查!");
            }

            //判断课程信息是否存在
            Ktinfo ktinfo =  ktinfoServiceImp.getKtinfoByNmae(kc);
            if(ktinfo==null){
                return toJson("002", "第"+(i+1)+"行课程信息不存在，请在课堂信息中添加!");
            }

            //插入数据库
            Teacher teacher = new Teacher();
            teacher.setName(jsxm);
            teacher.setPassword(psw);
            teacher.setPhone(phone);
            teacher.setSex(sex);
            teacher.setKc(ktinfo.getId());
            teacher.setZw(zw);
            teacher.setEmail(email);
            teacher.setSfz(sfz);
            teacher.setAddress(address);
            teacher.setNumber(jsbh);
            teacherServiceImp.addTeacher(teacher);
        }

        return toJson("000", "导入成功");
    }


    /**
     * 封装读取excel文件  存储数据库   读取教师导入模板
     * @param excelFile  @lys
     * @return
     * @throws IOException
     */
    @SuppressWarnings({ "rawtypes", "unchecked" })
    @ResponseBody
    @RequestMapping(value = "/admin/dkcj/exportDkcjs",produces="application/json; charset=utf-8")
    public String exportDkcjs( MultipartFile[] excelFile,HttpServletRequest request, HttpServletResponse response) {
        Map<String, Object> o = new HashMap<String, Object>();
        long startTime=System.currentTimeMillis();
        String originalFilename;
        try {
            if (excelFile == null || excelFile.length == 0) {
                return this.toJson("002", "文件上传失败，请重试！");
            }

            for (MultipartFile myfile : excelFile) {
                if (myfile.isEmpty()) {
                    return this.toJson("002", "文件上传失败，请重试！");
                } else {
                    originalFilename = myfile.getOriginalFilename();
                    if (!(originalFilename.endsWith(".xls") || originalFilename.endsWith(".xlsx"))) {
                        return toJson("002", "导入失败");
                    }

                    List<Map<String, Object>> list = null;
                    /*根据文件格式解析文件*/
                    if (originalFilename.endsWith(".xls")) {
                        list = readXLS(myfile);
                    } else if (originalFilename.endsWith(".xlsx")) {
                        list = readXLSX(myfile);
                    }
                    /* 获取数据 批量插入数据*/
                    if(list.size()>0){
                        return  insertDkcj(list);
                    }

                }
            }

            long hs=System.currentTimeMillis()-startTime;
            System.out.println("导入数据总耗时："+hs+"毫秒");

        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return "009";
        }
        return "000";

    }

    public String insertDkcj(List<Map<String, Object>> lists){
        SecurityUserInfo userDetails = (SecurityUserInfo) SecurityContextHolder.getContext()
                .getAuthentication()
                .getPrincipal();
        System.out.println("输出缓存的信息");
        String jsid = userDetails.getUserId();
        if(!userDetails.getRoleId().equals("4")){
            return toJson("002", "只有老师才能导入成绩!");
        }
        for (int i = 0; i < lists.size() ; i++) {
            String xsxh = getValue(lists.get(i).get("学生学号"));
            String xscj = getValue(lists.get(i).get("学生成绩"));
            if(xsxh.equals("")){
                return toJson("002", "第"+(i+1)+"行学生学号不可为空，请检查!");
            }
            if(xscj.equals("")){
                return toJson("002", "第"+(i+1)+"行学生成绩不可为空，请检查!");
            }

            //判断学生的学号是否存在
            User xsuser =  userServiceImp.getUserByUsername(xsxh);
            if(xsuser==null){
                return toJson("002", "第"+(i+1)+"行学生学号不存在，请检查!");
            }
            String xsid = xsuser.getId();

            //根据教师id获取老师所教课程的id和课程学分
            Teacher teacher =  teacherServiceImp.getTeacher(jsid);
            String xf = "0";
            String kcid = teacher.getKc();
            //判断分数是否超过60分
            if(Integer.parseInt(xscj)>60){
                xf = teacher.getKtfz();
            }
            //插入单科成绩表
            Dkcj dkcj = new Dkcj();
            dkcj.setKccj(xscj);
            dkcj.setXf(xf);
            dkcj.setKcid(kcid);
            dkcj.setJsid(jsid);
            dkcj.setXsid(xsid);
            dkcjServiceImp.addDkcj(dkcj);

            //插更新学分表
            Credit credit = creditServiceImp.getCreditByXsid(xsid);
            String bxkxf = "0";
            String xxkxf = "0";
            String rxkxf = "0";
            String gxkxf = "0";
            if(credit!=null){
                //查找该学生的单科成绩总和
                List<Map<String,String>> list = dkcjServiceImp.getDkcjByXsid(xsid);

                for (int j = 0; j < list.size(); j++) {
                    String ktlx = list.get(j).get("ktlx");
                    Object ktzfs = list.get(j).get("ktzf");
                    String ktzf = ktzfs.toString();
                    if(ktlx.equals("必修课")){
                        bxkxf = ktzf;
                    }
                    if(ktlx.equals("限选课")){
                        xxkxf = ktzf;
                    }
                    if(ktlx.equals("任选课")){
                        rxkxf = ktzf;
                    }
                    if(ktlx.equals("公选课")){
                        gxkxf = ktzf;
                    }
                }
                //说明存在则更新
                Credit credit1 = new Credit();
                credit1.setTitle("各类型学分情况");
                credit1.setXxkxf(xxkxf);
                credit1.setBxkxf(bxkxf);
                credit1.setRxkxf(rxkxf);
                credit1.setGxkxf(gxkxf);
                credit1.setXsid(xsid);
                creditServiceImp.setCreditByXsid(credit1);
            }else{
                //不存在 添加新数据
                String ktlx = dkcj.getKtlx();
                String newxf = dkcj.getXf();
                if(ktlx.equals("必修课")){
                    bxkxf = newxf;
                }
                if(ktlx.equals("限选课")){
                    xxkxf = newxf;
                }
                if(ktlx.equals("任选课")){
                    rxkxf = newxf;
                }
                if(ktlx.equals("公选课")){
                    gxkxf = newxf;
                }
                //INSERT INTO credit (id, title,bxkxf,xxkxf,rxkxf,gxkxf,name,xsid,djrq,lrsj)
                Credit credit1 = new Credit();
                credit1.setTitle("各类型学分情况");
                credit1.setXxkxf(xxkxf);
                credit1.setBxkxf(bxkxf);
                credit1.setRxkxf(rxkxf);
                credit1.setGxkxf(gxkxf);
                credit1.setName("");
                credit1.setXsid(xsid);
                credit1.setDjrq("");
                creditServiceImp.addCredit(credit1);
            }


        }

        return toJson("000", "导入成功");
    }












    // 导出老师模板
    public Object doExcelByMb2() {
        try {
            // 初始化 bs（数据库访问）、request（请求）,response（响应）,par（layui,table自定义参数）
            List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
            Map<String, Object> map1 = new HashMap<String, Object>();
            map1.put("jsbh", "201578");
            map1.put("jsxm", "李飞");
            map1.put("sex", "男");
            map1.put("psw", "123456");
            map1.put("kc", "Java");
            map1.put("zw", "教授");
            map1.put("email", "135784578@qq.com");
            map1.put("phone", "156784578");
            map1.put("sfz", "4509212000003153650");
            map1.put("address", "北京");
            list.add(map1);

            Map<String, Object> map = new HashMap<String, Object>();
            String[] cols = { "教师编号","教师姓名", "性别", "密码", "课程", "职称","邮箱","联系电话","身份证","住址"};
            String[] keys = { "jsbh","jsxm","sex","psw", "kc", "zw","email","phone","sfz","address"};
            map.put("fileName", "老师导入模板.xls");
            map.put("cols", cols);
            map.put("keys", keys);
            map.put("list", list);
            return map;
        } catch (Exception e) {
            e.printStackTrace();
            return "";
        }

    }




    // 导出学生模板
    public Object doExcelByMb() {
        try {
            // 初始化 bs（数据库访问）、request（请求）,response（响应）,par（layui,table自定义参数）
            List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
            Map<String, Object> map1 = new HashMap<String, Object>();
            map1.put("xsxh", "20200505325612");
            map1.put("xsxm", "张萌");
            map1.put("psw", "123456");
            map1.put("sex", "男");
            map1.put("rxnj", "2020");
            map1.put("zy", "计算机软件应用");
            map1.put("lxdh", "13545454545");
            map1.put("email", "135784578@qq.com");
            map1.put("sfz", "450921200001253650");
            map1.put("address", "北京朝阳一号");
            list.add(map1);

            Map<String, Object> map = new HashMap<String, Object>();
            String[] cols = { "学生学号","学生姓名", "密码", "性别", "入学年级", "专业","联系电话","邮箱","身份证","住址"};
            String[] keys = { "xsxh","xsxm","psw","sex", "rxnj", "zy","lxdh","email","sfz","address"};
            map.put("fileName", "学生导入模板.xls");
            map.put("cols", cols);
            map.put("keys", keys);
            map.put("list", list);
            return map;
        } catch (Exception e) {
            e.printStackTrace();
            return "";
        }

    }



    // 导出成绩成绩模板
    public Object doExcelByMbDkcj() {
        try {
            // 初始化 bs（数据库访问）、request（请求）,response（响应）,par（layui,table自定义参数）
            List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
            Map<String, Object> map1 = new HashMap<String, Object>();
            map1.put("xsxh", "201587845");
            map1.put("xscj", "100");
            list.add(map1);
            Map<String, Object> map = new HashMap<String, Object>();
            String[] cols = { "学生学号", "学生成绩"};
            String[] keys = {"xsxh","xscj"};
            map.put("fileName", "单科成绩导入模板.xls");
            map.put("cols", cols);
            map.put("keys", keys);
            map.put("list", list);
            return map;
        } catch (Exception e) {
            e.printStackTrace();
            return "";
        }

    }


    public String toJson(String code,String message){
        Map<String, Object> map=new HashMap<String, Object>();
        map.put("data", null);
        map.put("count", 0);
        map.put("msg", message);
        map.put("code", code);
        return toJSONString(map);
    }
    public static String toJSONString(Object o) {
        JSONWriter writer = new JSONWriter();
        writer.writeObject(o);
        return writer.toString();
    }

    public String getValue(Object obj){
        String result="";
        if(obj!=null&&!obj.toString().trim().equals("")&&!obj.toString().trim().equals("null")&&!obj.toString().trim().equals("undefiend")){
            result=obj.toString().trim();
        }
        return result;
    }




    /**
     * 解析.xls格式的excel文件
     * @param file
     * @return
     * @throws IOException
     */
    public List<Map<String, Object>> readXLS(MultipartFile file)  {
        InputStream is;
        List<Map<String, Object>> list = new ArrayList<>();
        try {
            is = file.getInputStream();
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);

            // 循环工作表Sheet
            // 循环行Row
            int errorNum = 0;
            int okNum = 0;
            String errorMsg = "";
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
            //第一行标题
            HSSFRow title = hssfSheet.getRow(0);


            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                Map<String, Object> map = new HashMap<String, Object>();
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {
                    for (int i = 0; i < hssfRow.getLastCellNum(); i++) {

                        HSSFCell txt = hssfRow.getCell(i);
                        if(txt!=null&&!txt.toString().equals("")) {
                            txt.setCellType(XSSFCell.CELL_TYPE_STRING);
                            //赋值给map
                            map.put(title.getCell(i).toString(), txt.toString());
                        }

                    }
                }
                okNum += 1;
                //添加map进list
                list.add(map);

            }
            message = "导入结果:&成功导入" + okNum + "条数据 失败" + errorNum + "条&错误记录:&"
                    + errorMsg;
            System.out.println(message);
            if(is!=null){
                is.close();
            }

            return list;

        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;

    }

    /**
     * 解析.xlsx格式的excel文件
     * @param file
     * @return
     * @throws IOException
     */
    public List<Map<String, Object>> readXLSX(MultipartFile file) throws IOException {
        InputStream is = file.getInputStream();
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        List<Map<String, Object>> list = new ArrayList<>();
        // 循环工作表Sheet
        // 循环行Row
        int errorNum = 0;
        int okNum = 0;
        String errorMsg = "";
        XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
        //第一行标题
        XSSFRow title = xssfSheet.getRow(0);

        for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
            Map<String, Object> map = new HashMap<String, Object>();
            XSSFRow xssfRow = xssfSheet.getRow(rowNum);
            if (xssfRow != null) {
                for (int i = 0; i < xssfRow.getLastCellNum(); i++) {

                    XSSFCell txt = xssfRow.getCell(i);
                    if(txt!=null&&!txt.toString().equals("")) {
                        txt.setCellType(XSSFCell.CELL_TYPE_STRING);
                        //赋值给map
                        map.put(title.getCell(i).toString(), txt.toString());
                    }
                }
            }
            okNum += 1;
            list.add(map);
        }
        message = "导入结果:&成功导入" + okNum + "条数据 失败" + errorNum + "条&错误记录:&"
                + errorMsg;
        System.out.println(message);
        if(null!=is){
            is.close();
        }
        return list;
    }








    /**
     * 创建excel文档，
     *
     * @param list        数据
     * @param keys        list中map的key数组集合
     * @param columnNames excel的列名
     */
    public static Workbook createWorkBook(List<Map<String, Object>> list, String[] keys, String columnNames[]) {
        // 创建excel工作簿
        Workbook wb = new HSSFWorkbook();
        // 创建第一个sheet（页），并命名
        Sheet sheet = wb.createSheet("Sheet1");
        // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
        for (int i = 0; i < keys.length; i++) {
            sheet.setColumnWidth((short) i, (short) (55.7 * 150));
        }

        // 创建第一行
        Row row = sheet.createRow((short) 0);

        //设置行高
        row.setHeight((short) (19.5*30));

        // 创建两种单元格格式
        CellStyle cs = wb.createCellStyle();
        CellStyle cs2 = wb.createCellStyle();

        // 创建两种字体
        Font f = wb.createFont();
        Font f2 = wb.createFont();

        // 创建第一种字体样式（用于列名）
        f.setFontHeightInPoints((short) 10);
        f.setColor(IndexedColors.BLACK.getIndex());
        f.setBoldweight(Font.BOLDWEIGHT_BOLD);

        // 创建第二种字体样式（用于值）
        f2.setFontHeightInPoints((short) 10);
        f2.setColor(IndexedColors.BLACK.getIndex());

        // 设置第一种单元格的样式（用于列名）
        cs.setFont(f);
        cs.setBorderLeft(CellStyle.BORDER_THIN);
        cs.setBorderRight(CellStyle.BORDER_THIN);
        cs.setBorderTop(CellStyle.BORDER_THIN);
        cs.setBorderBottom(CellStyle.BORDER_THIN);
        cs.setAlignment(CellStyle.ALIGN_CENTER);

        // 设置第二种单元格的样式（用于值）
        cs2.setFont(f2);
        cs2.setBorderLeft(CellStyle.BORDER_THIN);
        cs2.setBorderRight(CellStyle.BORDER_THIN);
        cs2.setBorderTop(CellStyle.BORDER_THIN);
        cs2.setBorderBottom(CellStyle.BORDER_THIN);
        cs2.setAlignment(CellStyle.ALIGN_CENTER);
        // 设置列名
        for (int i = 0; i < columnNames.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(columnNames[i]);
            cell.setCellStyle(cs);
        }
        // 设置每行每列的值
        for (short i = 0; i < list.size(); i++) {
            // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
            // 创建一行，在页sheet上
            Row row1 = sheet.createRow(i + 1);
            // 在row行上创建一个方格
            for (short j = 0; j < keys.length; j++) {
                Cell cell = row1.createCell(j);
                cell.setCellValue(list.get(i).get(keys[j]) == null ? " " : list.get(i).get(keys[j]).toString());
                cell.setCellStyle(cs2);
            }
        }
        return wb;
    }








}

